﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace Tutorial_Day1
{
    public class DBManager : IDisposable
    {

        //Added by FXJ on 29 May 2014
        private string ConStr = "";
        private SqlConnection Cnn;

        public DBManager()
        {
            this.ConStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            this.Cnn = new SqlConnection();
            this.Cnn.ConnectionString = this.ConStr;
        }
        /// <summary>
        /// Connects to DB if Connection is not establisted already
        /// </summary>
        public void ConnectToDB()
        {
            try
            {
                this.Cnn.ConnectionString = this.ConStr;
                this.Cnn.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Executes INSERT / UPDATE / DELETE Queries
        /// </summary>
        /// <param name="sSqlQuery"></param>
        /// <returns>Returns number of Rows Affected</returns>
        public int ExecuteNonQuery(string sSqlQuery)
        {
            int iAffectedRows = 0;
            try
            {
                if (this.Cnn.State == ConnectionState.Closed)
                {
                    ConnectToDB();
                }
                SqlCommand ObjCmd = new SqlCommand(sSqlQuery, this.Cnn);
                iAffectedRows = ObjCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {

                throw (ex);

            }
            finally
            {
                if (this.Cnn.State == ConnectionState.Open)
                {
                    Cnn.Close();
                }
            }
            return iAffectedRows;
        }
        /// <summary>
        /// Executes a Scalar Query that returns a single value as object
        /// </summary>
        /// <param name="sSqlQuery">Scalar Query to be executed</param>
        /// <returns></returns>
        public object ExecuteScalarQuery(string sSqlQuery)
        {
            object oValue = null;
            try
            {
                if (this.Cnn.State == ConnectionState.Closed)
                {
                    ConnectToDB();
                }
                SqlCommand ObjCmd = new SqlCommand(sSqlQuery, this.Cnn);
                oValue = ObjCmd.ExecuteScalar();

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (this.Cnn.State == ConnectionState.Open)
                {
                    Cnn.Close();
                }
            }
            return oValue;
        }
        /// <summary>
        /// Any SQL Select Statement that returns set of records.
        /// </summary>
        /// <param name="sSqlQuery"></param>
        /// <returns></returns>
        public DataTable FetchTable(string sSqlQuery)
        {
            DataTable dt = new DataTable();
            try
            {
                if (this.Cnn.State == ConnectionState.Closed)
                {
                    ConnectToDB();
                }
                using (SqlCommand myCommand = new SqlCommand(sSqlQuery, this.Cnn))
                {
                    myCommand.CommandType = CommandType.Text;
                    SqlDataAdapter da = new SqlDataAdapter(myCommand);
                    da.Fill(dt);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (this.Cnn.State == ConnectionState.Open)
                {
                    Cnn.Close();
                }
            }
            return dt;
        }


        public void Dispose()
        {
            GC.Collect();
        }
    }
}
